package edu.cs.usask.ca.silver.persistence.sql;

/**
 * User Sql queries.
 * 
 * @author Yudi Xue
 * 
 */
public interface SQLUserStringsI {
	// When a new project is created u_nextMvIdPerProject is initialised to 1.
	public static final String USER_INSERT = "INSERT INTO SUser (u_identity, u_update_time, u_first_name, u_last_name, u_email, u_pass, u_creationDate)"
			+ "VALUES ('%s', CURRENT_TIMESTAMP, '%s', '%s', '%s', '%s', CURRENT_TIMESTAMP)";

	public static final String USER_INSERT_WITH_PRIVATE_GROUP = "INSERT INTO SUser (u_identity, u_update_time, u_first_name, u_last_name, u_email, u_pass, u_group_identity, u_creationDate)"
			+ "VALUES ('%s', CURRENT_TIMESTAMP, '%s', '%s', '%s', '%s', '%s', CURRENT_TIMESTAMP)";

	public static final String USER_SELECT = "SELECT u_identity, u_update_time, u_first_name, u_last_name, u_email, u_pass, u_creationDate"
			+ "FROM SUser WHERE u_identity = '%s'";

	public static final String USER_SELECT_BASIC_INFO = "SELECT u_identity, u_first_name, u_last_name, u_email, u_pass FROM SUser WHERE u_identity = '%s'";

	public static final String USER_SELECT_ANY = "SELECT * FROM SUser";
	public static final String USER_SELECT_ALLIDS = "SELECT u_identity FROM SUser";

	public static final String USER_SELECT_LAST_UPDATE_TIME = "SELECT u_update_time FROM SUser WHERE u_identity = '%s'";
	public static final String USER_SELECT_FIRST_NAME = "SELECT u_first_name FROM SUser WHERE u_identity = '%s'";
	public static final String USER_SELECT_LAST_NAME = "SELECT u_last_name FROM SUser WHERE u_identity = '%s'";
	public static final String USER_SELECT_PASSWD = "SELECT u_pass FROM SUser WHERE u_identity = '%s'";
	public static final String USER_SELECT_PASSWD_WITH_EMAIL = "SELECT u_pass FROM SUser WHERE u_email = '%s'";
	public static final String USER_SELECT_EMAIL = "SELECT u_email FROM SUser WHERE u_identity = '%s'";
	public static final String USER_SELECT_CREATIONDATE = "SELECT u_creationDate FROM SUser WHERE u_identity = '%s'";
	public static final String USER_SELECT_PRIVATE_GROUP = "SELECT u_group_identity FROM SUser WHERE u_identity = '%s'";
	public static final String USER_SELECT_IDENTITY_WITH_EMAIL_AND_PASS = "SELECT u_identity FROM SUser WHERE u_email = '%s' AND u_pass = '%s'";

	// DERBY does not support `LIMIT` -check
	// http://db.apache.org/derby/faq.html#limit
	// public static final String USER_SELECT_MOSTRECENTMVERSIONID =
	// "SELECT mv_identityFROM MVersion WHERE mv_u_identity = '%s' " +
	// "ORDER BY mv_creationDate DESC LIMIT 1";
	// public static final String USER_SELECT_MOSTRECENTMVERSIONID =
	// "SELECT mv_identityFROM (SELECT ROW_NUMBER() OVER() AS rownum,
	// mv_identity
	// FROM MVersion WHERE mv_u_identity = '%s' ORDER BY mv_creationDate DESC )
	// AS tmp MVersion WHERE rownum = 1";

	public static final String USER_SET_FIRST_NAME = "UPDATE SUser SET u_first_name = '%s' ,u_update_time = CURRENT_TIMESTAMP WHERE u_identity = '%s'";
	public static final String USER_SET_LAST_NAME = "UPDATE SUser SET u_last_name = '%s', u_update_time = CURRENT_TIMESTAMP WHERE u_identity = '%s'";
	public static final String USER_SET_EMAIL = "UPDATE SUser SET u_email = '%s' ,u_update_time = CURRENT_TIMESTAMP WHERE u_identity = '%s'";
	public static final String USER_SET_PASSWD = "UPDATE SUser SET u_pass = '%s' ,u_update_time = CURRENT_TIMESTAMP WHERE u_identity = '%s'";
	public static final String USER_SET_PRIVATE_GROUP_ID = "UPDATE SUser SET u_group_identity = '%s' ,u_update_time = CURRENT_TIMESTAMP WHERE u_identity = '%s'";

	public static final String USER_DELETE = "DELETE FROM SUser WHERE u_identity = '%s'";

	public static final String USER_SELECT_PARTICIPATED_GROUP_IDS = "SELECT u2group_g_identity FROM U2Group WHERE u2group_u_identity = '%s'";
	public static final String USER_SELECT_PARTICIPATED_TIME_TO_THE_GROUP = "SELECT u2group_join_time FROM U2Group WHERE u2group_u_identity = '%s' AND u2group_g_identity = '%s'";

	public static final String USER_PARTICIPATES_GROUP = "INSERT INTO U2group (u2group_u_identity, u2group_g_identity, u2group_join_time) "
			+ "VALUES ('%s', '%s', CURRENT_TIMESTAMP)";

	public static final String USER_SELECT_OWNING_GROUP_IDS = "SELECT g_identity FROM SGRoup WHERE g_owner_identity = '%s'";
}
